/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package medico;

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfWriter;
import condivisa.Persona;
import condivisa.Persona.tipo_persona;
import java.io.FileNotFoundException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.NamingException;
import javax.servlet.jsp.jstl.sql.ResultSupport;
import connezione.PooledConnection;
import hirondelle.date4j.DateTime;
import java.io.File;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.TimeZone;
import java.util.TreeMap;
import javax.faces.context.FacesContext;
import javax.servlet.ServletContext;
import javax.servlet.jsp.jstl.sql.Result;
import login.BeanUser;
import mail.Mail;

/**
 *
 * @author sestari
 */
public class BeanMedico {

    PooledConnection conn;
    BeanUser user;
    private Integer idmedico = 0;
    private Integer idpaziente = 0;
    private Persona paziente;

    public Persona getPaziente() {
        return paziente;
    }

    public String getMaildamod() {
        return maildamod;
    }

    public void setMaildamod(String maildamod) {
        this.maildamod = maildamod;
    }

    public String getPassdamod() {
        return passdamod;
    }

    public void setPassdamod(String passdamod) {
        this.passdamod = passdamod;
    }
    private String passdamod;
    private String maildamod;

    /** Creates a new instance of BeanMedico */
    public BeanMedico() {
        conn = (PooledConnection) ((BeanUser) FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("user")).getCon();
        user = (BeanUser) FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get("user");
        setIdmedico(user.getId());
        paziente = new Persona();
    }

    public Result getRichiamiAll() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT Anagrafica.nome, Anagrafica.cognome , Visita.* FROM Visita, Anagrafica where Anagrafica.id = Visita.id_paziente and id_medico=\"" + getIdmedico() + "\" and  richiami > \"0\" ");
        return ResultSupport.toResult(result);
    }

    public Result getVisitaPaziente() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT * FROM Visita where richiami < \"1\" and id_paziente = \"" + getIdpaziente() + "\"");
        return ResultSupport.toResult(result);
    }

    public Result getPazientiAll() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT * FROM Persona where id_medico=\"" + getIdmedico() + "\" order by nome ASC");
        return ResultSupport.toResult(result);

    }

    public Result getaggVisita() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT * FROM Persona where id_medico=\"" + getIdmedico() + "\" and id =\"" + getIdpaziente() + "\"");
        return ResultSupport.toResult(result);

    }

    //Visite programmate dal medico per oggi
    public Result getmodVisita() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        DateTime now = DateTime.now(TimeZone.getDefault());

        String command = "SELECT *  FROM `db_user15`.`Listavisita` where  tipo_scadenza=\"PROGRAMMATA_MEDICO\" and id_medico=\"" + getIdmedico() + "\" and id_paziente =\"" + getIdpaziente() + "\" and   CAST( now() as date) = CAST(  data_scadenza as date)  and richiami >= 0 LIMIT 1";
        ResultSet result = stmt.executeQuery(command);
        return ResultSupport.toResult(result);

    }

    public TreeMap<String, String> getNomeComboBox() throws SQLException, NamingException {
        TreeMap<String, String> myModelValues = new TreeMap<String, String>();
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT id,nome,cognome FROM Persona where id_medico=\"" + getIdmedico() + "\"  order by nome ASC");


        while (result.next()) {
            myModelValues.put(result.getString(2) + " " + result.getString(3), result.getString(1));

        }
        return myModelValues;
    }

    //soltanto le persone che hanno fato delle visite
    public TreeMap<String, String> getNomeComboBoxVisite() throws SQLException, NamingException {
        TreeMap<String, String> myModelValues = new TreeMap<String, String>() {
        };
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT id,nome,cognome FROM Persona where id_medico=\"" + getIdmedico() + "\" and  ( Persona.id in (select distinct id_paziente from Visita) )  order by nome ASC");


        while (result.next()) {
            myModelValues.put(result.getString(2) + " " + result.getString(3), result.getString(1));

        }
        return myModelValues;
    }

    public Result getNomeSemPass() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT * FROM Anagrafica where id =\"" + getIdpaziente() + "\"");
        return ResultSupport.toResult(result);

    }

    public Map<String, String> getNomeSemPassComboBox() throws SQLException, NamingException {
        Map<String, String> myModelValues = new HashMap<String, String>();
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT id,nome,cognome FROM Anagrafica where  id not in (Select id_persona FROM Utenti) order by nome ASC");


        while (result.next()) {
            myModelValues.put(result.getString(2) + " " + result.getString(3), result.getString(1));

        }
        return myModelValues;
    }

    public Integer getIdmedico() {
        return idmedico;
    }

    public void setIdmedico(Integer idmedico) {
        this.idmedico = idmedico;
    }

    public Integer getIdpaziente() {
        return idpaziente;
    }

    public void setIdpaziente(Integer idpaziente) {
        this.idpaziente = idpaziente;
    }

    public String modDataAnagrafica() throws SQLException {

        Statement stmt = conn.createStatement();
        String command = "UPDATE `db_user15`.`Anagrafica`  SET "
                + " `indirizzo` = \"" + paziente.getIndirizzo() + "\""
                + " ,`cap` = \"" + paziente.getCap() + "\""
                + " ,`latitudine` = \"" + paziente.getCooX() + "\""
                + " ,`longitudine` = \"" + paziente.getCooY() + "\""
                + " ,`e_mail` = \"" + paziente.getEmail() + "\""
                + " ,`cellulare` = \"" + paziente.getCelullare() + "\""
                + " WHERE id = \"" + getIdpaziente() + "\"";
        stmt.executeUpdate(command);
        return "back";
    }

    public Result getAnagrafica() throws SQLException, NamingException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Persona WHERE id =  " + getIdpaziente());
        if (rs.next()) {
            paziente.setId(rs.getInt("id"));
            paziente.setId_medico(rs.getInt("id_medico"));
            paziente.setNome(rs.getString("nome"));
            paziente.setCognome(rs.getString("cognome"));
            paziente.setSesso(rs.getString("sesso").charAt(0));
            paziente.setLuogo_nascita(rs.getString("luogo_nascita"));
            paziente.setIndirizzo(rs.getString("indirizzo"));
            paziente.setCap(rs.getInt("cap"));
            paziente.setCooX(rs.getLong("latitudine"));
            paziente.setCooY(rs.getLong("longitudine"));
            paziente.setId_padre(rs.getInt("id_padre"));
            paziente.setId_madre(rs.getInt("id_madre"));
            paziente.setEmail(rs.getString("e_mail"));
            paziente.setCelullare(rs.getString("cellulare"));
            paziente.setId_capo_vilaggio(rs.getInt("id_capo_villaggio"));
            paziente.setFoto(rs.getString("foto"));
            paziente.setTipo_persona(tipo_persona.valueOf(rs.getString("tipo_persona")));

        }
        rs.beforeFirst();
        return ResultSupport.toResult(rs);
    }

    public void generaPdf(String nomeMedico, String idMedico, String data, String idVisita, String descrizione, String emailPaziente) {

        String nomefile = idMedico + "_" + data + "_" + idVisita + ".pdf";
        ServletContext servletContext = (ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext();
        String realPath = servletContext.getRealPath("/pdfVisite");

        Document document = new Document();
        try {
            try {
                PdfWriter.getInstance(document, new FileOutputStream(new File(realPath, nomefile)));

                document.open();
                document.add(new Paragraph("Nome Medico: " + nomeMedico));

                document.add(new Paragraph("Data: " + data));

                document.add(new Paragraph("Descrizione: " + descrizione));




                document.close();
            } catch (FileNotFoundException ex) {
                Logger.getLogger(BeanMedico.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (DocumentException ex) {
            Logger.getLogger(BeanMedico.class.getName()).log(Level.SEVERE, null, ex);
        }


        File f = new File(realPath, nomefile);

        if (f.exists()) {

            Mail m = new Mail(emailPaziente, "PDF Visita", f, "", "", "unitn", "PDF Visita");
            new Thread(m).start();



        }



    }

    public String getCountVisiteSpontanea() throws SQLException {
        String res = "";
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT count(id_medico)  FROM `db_user15`.`Visita` WHERE data_scadenza >= CAST( now() as date) AND richiami = -1 AND tipo_visita = \"SPONTANEA\" AND id_medico = " + getIdmedico() + ";");
        result.next();
        res = result.getString(1);


        return res;
    }

    public String getCountVisiteProgrammate() throws SQLException {
        String res = "";
        Statement stmt = conn.createStatement();

        ResultSet result = stmt.executeQuery("SELECT count(id_medico)  FROM `db_user15`.`Visita` WHERE data_scadenza >= CAST( now() as date) AND richiami = -1 AND tipo_visita = \"PROGRAMMATA\" AND id_medico = " + getIdmedico() + ";");
        result.next();
        res = result.getString(1);


        return res;
    }

    public String getCountVisiteVaccinazioni() throws SQLException {
        String res = "";
        Statement stmt = conn.createStatement();
        ResultSet result = stmt.executeQuery("SELECT count(id_medico)  FROM `db_user15`.`Visita` WHERE data_scadenza >= CAST( now() as date) AND richiami = -1 AND tipo_visita = \"VACCINAZIONE\" AND id_medico = " + getIdmedico() + ";");
        result.next();
        res = result.getString(1);


        return res;
    }
}
